package com.teama4.shop.utils;


import java.sql.*;

public class TestUtil {

    public void sql() {
        String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        String DB_URL = "jdbc:mysql://localhost:3306/mall";
        String USER = "root";
        String PASS = "123456";

        Connection conn = null;
        Statement stmt = null;
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);

            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            // 执行查询
            System.out.println(" 实例化Statement对象...");
            String sql = "select * from commodity";
            stmt = conn.createStatement();
//            stmt.executeUpdate(sql);
            ResultSet resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getInt(1));
            }

            // 完成后关闭
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // 处理 JDBC 错误
            se.printStackTrace();
        } catch (Exception e) {
            // 处理 Class.forName 错误
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }// 什么都不做
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }

    public void makePeople() throws ClassNotFoundException, SQLException {
        int[] people = new int[18000];
        int[] rank;

        int parameter1, parameter2, parameter3, r = 0;


        String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        String DB_URL = "jdbc:mysql://localhost:3306/mall";
        String USER = "root";
        String PASS = "123456";

        Connection conn = null;
        Statement stmt = null;
        // 注册 JDBC 驱动
        Class.forName(JDBC_DRIVER);

        // 打开链接
        conn = DriverManager.getConnection(DB_URL, USER, PASS);


        for (int i = 0; i < 100; i++) {
            people[i] = (int) (Math.random() * 18) + 1;
            System.out.println(i);
            // 执行查询
            int n = (int) (Math.random() * 200);
            for (int j = 0; j < n; j++) {
                int commodityId = 0;
                String sql = "select id from cate where second_category = " + people[i];
                stmt = conn.createStatement();
                ResultSet resultSet = stmt.executeQuery(sql);
                resultSet.first();
                int first = resultSet.getInt(1);
                resultSet.last();
                int last = resultSet.getInt(1);
                int cate = (int) (Math.random() * (last - first + 2)) + first - 1;
                //得到二级类别序列

                // 如果比first小1直接去二级类别找
                if (cate == first - 1) {
                    cate = people[i];
                }

                //去找三级类别的商品
                sql = "select id from cate where second_category =" + cate;
                resultSet = stmt.executeQuery(sql);
                resultSet.first();
                first = resultSet.getInt(1);
                resultSet.last();
                last = resultSet.getInt(1);
                cate = (int) (Math.random() * (last - first + 1)) + first;
                //以上找出三级类别

                sql = "select id from commodity where catetory_id = " + cate;
                resultSet = stmt.executeQuery(sql);
                if (resultSet.next()) {
                    resultSet.last();
                    int row = resultSet.getRow();
                    resultSet.first();
                    for (int k = 0; k < (int) (Math.random() * row); k++) {
                        resultSet.next();
                    }
                    commodityId = resultSet.getInt(1);
                    System.out.println(commodityId);

                    parameter1 = (int) (Math.random() * 8) + 1;
                    parameter2 = (int) (Math.random() * 6);
                    parameter3 = (int) (Math.random() * 4);
                    r = parameter1 * 1 + parameter2 * 2 + parameter3 * 4;
                    System.out.println(r);
                    sql = "insert into model values(?,?,?)";
                    PreparedStatement preparedStatement = conn.prepareStatement(sql);
                    preparedStatement.setInt(1, i + 1);
                    preparedStatement.setInt(2, commodityId);
                    preparedStatement.setInt(3, r);
                    preparedStatement.executeUpdate();
                }

            }
        }
        // 完成后关闭
        stmt.close();
        conn.close();
    }
}
